package com.dao.impl;


import com.dao.SongListService;
import com.entity.Member;
import com.entity.SongList;
import com.util.JdbcUtil;
import com.util.PageUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 歌单 服务实现类
 * </p>
 *
 * @author lzy
 * @since 2022-03-23
 */
public class SongListServiceImpl implements SongListService {

//    插入
    @Override
    public int insert(SongList songList) throws SQLException {
        String sql="insert into song_list (member_id,style_id,title,song_list_image,introduction,tags,is_deleted,create_time) values(?,?,?,?,?,?,?,0,NOW())";
        Object[] value=new Object[]{songList.getMemberId(),songList.getStyleId(),songList.getTitle(),songList.getSongListImage(),songList.getIntroduction(),songList.getTags()};
        return JdbcUtil.insertOrUpdateOrDelete(sql,value);
    }
//查询
    @Override
    public List queryData(SongList songList) throws IllegalAccessException, SQLException, InstantiationException {
        String sql="select song_list.song_list_id,title,song_list_image,introduction,tags,song_list.is_deleted sldelest,song_list.style_id,style_name,song_list.member_id,nickname,countsum.collectsumnum,songnum.songsum from style,song_list,member,(select song_list_id sli,count(song_list_id) collectsumnum from collect group by song_list_id) countsum,(select COUNT(song_list_mid.song_list_id) songsum,song_list_mid.song_list_id from song_list,song_list_mid GROUP BY song_list_mid.song_list_id) songnum where song_list.song_list_id=countsum.sli and song_list.member_id=member.member_id and song_list.style_id=style.style_id and song_list.song_list_id=songnum.song_list_id and song_list.is_deleted=0";
        return JdbcUtil.queryData(sql,Member.class);
    }
//更新
    @Override
    public int update(SongList songList) throws SQLException {
        String sql="UPDATE song_list SET member_id = ?,style_id= ?,title= ?,song_list_image= ?,introduction= ?,tags= ?,update_time= NOW() WHERE song_list_id = ?";
        Object[] value=new Object[]{songList.getMemberId(),songList.getStyleId(),songList.getTitle(),songList.getSongListImage(),songList.getIntroduction(),songList.getTags(),songList.getSongListId()};
        return JdbcUtil.insertOrUpdateOrDelete(sql,value);
    }
//    删除
    @Override
    public int deleteById(Object id) throws SQLException {
        String sql="UPDATE song_list SET is_deleted= 1,update_time = now() WHERE song_list_id = ?";
        return JdbcUtil.insertOrUpdateOrDelete(sql,new Object[]{id});
    }
//分页查询
    @Override
    public PageUtil<SongList> queryDataBypage(int currentPage, int pageSize, SongList songList) throws IllegalAccessException, SQLException, InstantiationException {
        String sql="select song_list.song_list_id,title,song_list_image,introduction,tags,song_list.is_deleted sldelest,song_list.style_id,style_name,song_list.member_id,nickname,countsum.collectsumnum,songnum.songsum,song_list.create_time,song_list.update_time from song_list left join style on song_list.style_id=style.style_id left join member on song_list.member_id=member.member_id left join(select song_list_id sli,count(song_list_id) collectsumnum from collect group by song_list_id) countsum on song_list.song_list_id=countsum.sli left join(select COUNT(song_list_mid.song_list_id) songsum,song_list_mid.song_list_id from song_list,song_list_mid GROUP BY song_list_mid.song_list_id) songnum on song_list.song_list_id=songnum.song_list_id where song_list.is_deleted=0";
        ArrayList values = new ArrayList();
        if (songList != null){
            if (songList.getTitle() != null && !"".equals(songList.getTitle())){
                sql += "and title like ? ";
                values.add( "%"+songList.getTitle() +"% ");
            }
        }
        return JdbcUtil.queryDataByPage(sql, SongList.class,currentPage,pageSize,values.toArray());
    }

    @Override
    public int deleteByIdS(List<Object> obj) throws SQLException {
        String sql = "update song_list set is_deleted = 1 ,update_time = now() where song_list_id in (";
        for (int i = 0; i < obj.size();i++) {
            sql += "?,";
        }
        String substring = sql.substring(0,sql.length()-1);
        substring = substring + ")";
        return JdbcUtil.insertOrUpdateOrDelete(substring,obj.toArray());
    }
}
